package com.zhike.b2b.job.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.zhike.b2b.base.BaseService;
import com.zhike.b2b.job.IJobService;
import com.zhike.b2b.model.ClassVo;
import com.zhike.b2b.model.JobClassVo;
import com.zhike.b2b.model.JobVo;
import com.zhike.b2b.model.ProductClassVo;
import com.zhike.b2b.util.ClassUtil;

@Service
public class JobService extends BaseService implements IJobService {

	/* (non-Javadoc)
	 * @see com.zhike.b2b.job.impl.IJobService#queryJobClzs(java.lang.String, java.lang.String, java.lang.String)
	 */
	public List<ClassVo> queryJobClzs(String clzId,String limitStr,String clzLevel)
	{
		List<ClassVo> lpcv = jt.query(getSqlstr(clzId,limitStr,"","",""),  new RowMapper<ClassVo>() {
			 public JobClassVo mapRow(ResultSet rs, int rowNum)
				throws SQLException {
				 JobClassVo pcv = new JobClassVo();
				 pcv.setClassId(rs.getString("class_id"));
				 pcv.setListRule(rs.getString("list_rule"));
				 pcv.setStorePath(rs.getString("store_path"));
				 pcv.setHomeName(rs.getString("home_name"));
			
				 pcv.setClassName(rs.getString("class_name"));
				 pcv.setListUrl(ClassUtil.getListUrl(pcv, 1));
			
				 return pcv;
			}});
		return lpcv;
	}
	/* (non-Javadoc)
	 * @see com.zhike.b2b.job.impl.IJobService#queryJobs(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	public List<JobVo> queryJobs(String classId,String limitStr,String orderCln) {
	   StringBuffer sql = new StringBuffer("select distinct b.job_id as id,b.title,b.title as jobs_name,b.class_id,jc.list_rule,jc.store_path,jc.article_rule ");
	   
       sql.append(" from jobs b  inner join jobs_class jc on jc.class_id=b.class_id where b.validity=1 and b.remove_tag=1 and b.start_date <=now() and b.end_date >=now() ");
	    if (!classId.equals(""))
	    	sql.append(" and b.class_id like '" + classId + "%'");
	    if (orderCln.equals(""))
	        sql.append("  order by b." +orderCln +" desc");
	    if (!limitStr.equals(""))
	    	sql.append(" limit " + limitStr );
		List<JobVo> lnc = jt.query(sql.toString(), new RowMapper<JobVo>() {
			public JobVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				JobVo jv = new JobVo();
				jv.setClassId(rs.getString("class_id"));
				jv.setJobId(rs.getString("id"));
				jv.setJobName(rs.getString("jobs_name"));
				jv.setTitle(rs.getString("title"));
				jv.setListRule(rs.getString("list_rule"));
				jv.setStorePath(rs.getString("store_path"));
				jv.setArticleRule(rs.getString("article_rule"));
			
				jv.setUrl(ClassUtil.getObjUrl(jv, jv.getJobId()));
				return jv;
			}
		});
		return lnc;
	}

	
	  private String getProdSqlstr(String orderStr, String limitStr, String commend, String classId, String custId, String isHot, String isGood, String levelId, String type, String custType)
	  {
	    StringBuffer sql = new StringBuffer("select distinct p.product_id,p.product_name,p.attach_id,p.class_id,p.small_img,p.big_img,p.commend,p.good_product,p.list_top,p.num,ai.area_name ");
	    
	    StringBuffer subSql = new StringBuffer(" from(select p.attach_id,p.product_id,p.cust_id,p.area_id,p.product_name,p.class_id,p.small_img,p.big_img,p.commend,p.good_product,p.list_top,p.num,p.publish_user_id");
	   
	    subSql.append(" from product p inner join product_class pc on pc.class_id=p.class_id where p.display_start_date <=now() and p.display_end_date >=now() and p.display=1 and p.validity =1 ");
	    if (!classId.equals(""))
	      subSql.append(" and p.class_id like '" + classId + "%'");
	    if (!commend.equals(""))
	      subSql.append(" and p.commend=" + commend);
	    
	    if (!isHot.equals(""))
	      subSql.append(" and p.hot_product=" + isHot);
	    if (!isGood.equals(""))
	      subSql.append(" and p.good_product=" + isGood);
	    if (!custId.equals(""))
	      subSql.append(" and p.cust_id=" + custId);
	    if (orderStr.equals(""))
	    {
	      subSql.append("  order by p.commend  desc  ,p.good_product desc,p.hot_product desc ,p.list_top desc,p.publish_date desc ");
	    }else
	    {
	      
	    }
	    if (!limitStr.equals(""))
	      subSql.append(" limit " + limitStr);
	    sql.append(subSql);
	    sql.append(" ) p inner join company c on c.cust_id=p.cust_id ");
	    if (!levelId.equals(""))
	      sql.append(" and c.level_id=" + levelId);
	    if (!type.equals(""))
	      sql.append(" and c.type=" + type);
	    if (!custType.equals(""))
	      sql.append(" and c.cust_type=" + custType);
	    sql.append(" inner join user_info u on p.publish_user_id=u.user_id left join area_info ai on substr(p.area_id,1,6)=ai.area_id ");
	    logger.debug("当前product查询的语句为：" + sql.toString());
	    return sql.toString();
	  }

	/**
	 * 获得查询产品(供应)类别SQL
	 * @param clzLevel
	 * @param clzType
	 * @param page
	 * @param custId
	 * @param commend
	 * @return
	 */
	private String getSqlstr(String clzType, String page, String custId, String commend,String clzLevel)
	  {
	    if (clzType.indexOf("recommended-") > -1)
	    {
	      clzType = clzType.replaceAll("recommended-", "");
	    }
	    String length = "3";
	    int i = 0;
	    if (!clzLevel.equals(""))
	      i = Integer.parseInt(clzLevel);
	    logger.debug("class_level:" + clzLevel);
	    logger.debug("classtype:" + clzType);
	    switch (i)
	    {
	    case 1:
	      length = "3";
	      break;
	    case 2:
	      length = "6";
	      break;
	    case 3:
	      length = "10";
	      break;
	    case 4:
	      length = "15";
	      break;
	    case 5:
	      length = "20";
	      break;
	    default:
	      length = "0";
	    }
	    if (clzType.length() == 0 )
	    	length = "3";
	    else if (clzType.length() == 3)
	    	length = "6";
	    else if (clzType.length() == 6)
	    	length = "10";
	    else if (clzType.length() == 10)
	    	length = "15";
	    else if (clzType.length() == 15)
	    	length = "20";
	 
	    StringBuffer buf = new StringBuffer("select ifnull(p.datacount,0) datacount , bc.class_id,bc.class_name,bc.store_path,bc.home_name,bc.list_rule from product_class bc " +
	    		" left join (select count(left(class_id,"+length+")) as datacount,left(class_id,"+length+") class_id from product where  display_start_date <=now() and display_end_date >=now() and display=1 and validity =1  ");
	    if (commend.equals("1"))
	    	buf.append(" and commend= 1 ");
	    if (!custId.equals(""))
	    	buf.append(" and cust_id=" + custId);
	    buf.append("  group by left(class_id,"+length+")) as p on p.class_id =bc.class_id  where bc.checked=1 and  bc.remove_tag=1 and bc.class_id like '" + clzType + "%' ");  
	    buf.append(" and length(bc.class_id)=" + length );
	    if (!page.equals(""))
	    	buf.append(" limit " + page);
	  	    
	    logger.debug("产品类别："+buf.toString());
	    return buf.toString();
	  }
}
